package com.tbsoft.ehr.dao.staffFile;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.constants.AuditStatus;
import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.ServiceCustomer;
import com.tbsoft.ehr.model.StaffInfo;

public class StaffFileDaoImpl extends BaseDaoImpl<StaffInfo> implements StaffFileDao {

	private static final Log log =LogFactory.getLog(StaffFileDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	public List<StaffInfo> getAllStaffInfo() {
		return getHibernateTemplate().executeFind(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session.createQuery("from StaffInfo staff").list();
			}
		});
	}

	/**
	 * 根据条件查询档案信息
	 */
	public List<StaffInfo> getStaffInfoByCondton(StaffInfo staff,
			String entryDateFrom, String entryDateTo,final int startIndex,final int count) {				
		
		StringBuilder queryCondton = new StringBuilder("from StaffInfo a where a.status<>'D' ");
		if (staff.getName() != null && !staff.getName().isEmpty()) {
			queryCondton.append("and (a.name like '%").append(staff.getName())
					.append("%' or a.userId like '%").append(staff.getName()).append("%') ");
		}
		if (staff.getServiceCustomer() != null ) {
			queryCondton.append("and a.serviceCustomer = '").append(staff.getServiceCustomer().getId()).append("' ");
		}
		if(staff.getAgency()!=null){
			queryCondton.append("and a.agency = '").append(staff.getAgency().getId()).append("' ");
		}
		if(staff.getDepartment()!=null){
			queryCondton.append("and a.department = '").append(staff.getDepartment().getId()).append("' ");
		}
		if(entryDateFrom!=null&&entryDateTo!=null){
			queryCondton.append("and a.entrydate between '").append(entryDateFrom).append("' and '").append(entryDateTo).append("' ");
		}
		
		if(staff.getAuditStatus()!=null&&!staff.getAuditStatus().isEmpty()){
			queryCondton.append("and a.auditStatus ='").append(staff.getAuditStatus()).append("' ");
		}
		
		if(staff.getWorkStatus()!=null&&!staff.getWorkStatus().isEmpty()){
			queryCondton.append("and a.workStatus ='").append(staff.getWorkStatus()).append("' ");
		}
		final String queryStr = queryCondton.toString();	
		List<StaffInfo> staffLst = getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session.createQuery(queryStr).setFirstResult(startIndex).setMaxResults(count).list();
			}
		});
		return staffLst;
	}

	public StaffInfo getStaffInfoById(String id) {
		StaffInfo si = (StaffInfo)this.getHibernateTemplate().get(StaffInfo.class, id);
		log.debug(si.getName()+"的账户条数为:"+si.getAccounts().size());		
		return si;
	}

	//设置离职
	public void updateWorkStatus(final StaffInfo si) {
		getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("update StaffInfo si set si.leavdate = ?,si.workStatus = 'WORK_OFF' where si.staffId = ?");
				q.setDate(0, si.getLeavdate());
				q.setString(1, si.getStaffId());
				return q.executeUpdate();
			}
		});
	}

	//根据员工姓名/id 查询员工信息
	@SuppressWarnings("unchecked")
	@Override
	public List<StaffInfo> getStaffFileByStaffName(final String name) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query q = session.createQuery("from StaffInfo si where si.name like '%"+name+"%' or si.userId like '%"+name+"%' ");
				//此处没有查询到数据将返回空的list而不是null
				 return q.list();
			}
		});	
	}

	@Override
	public int getStaffFileCount(StaffInfo staff, String entryDateFrom,String entryDateTo) {
		StringBuilder queryCondton = new StringBuilder(
				"SELECT COUNT(*) from StaffInfo a where a.status<>'D' ");		
		if (staff.getName() != null && !staff.getName().isEmpty()) {
			queryCondton.append("and (a.name like '%").append(staff.getName())
					.append("%' or a.userId like '%").append(staff.getName()).append("%') ");
		}
		if (staff.getServiceCustomer() != null) {
			queryCondton.append("and a.serviceCustomer = '").append(
					staff.getServiceCustomer().getId()).append("' ");
		}
		if (staff.getAgency() != null) {
			queryCondton.append("and a.agency = '").append(
					staff.getAgency().getId()).append("' ");
		}
		if (staff.getDepartment() != null) {
			queryCondton.append("and a.department = '").append(
					staff.getDepartment().getId()).append("' ");
		}
		if (entryDateFrom != null && entryDateTo != null) {
			queryCondton.append("and a.entrydate between '").append(
					entryDateFrom).append("' and '").append(entryDateTo)
					.append("' ");
		}

		if (staff.getAuditStatus() != null && !staff.getAuditStatus().isEmpty()) {
			queryCondton.append("and a.auditStatus ='").append(
					staff.getAuditStatus()).append("' ");
		}

		if (staff.getWorkStatus() != null && !staff.getWorkStatus().isEmpty()) {
			queryCondton.append("and a.workStatus ='").append(
					staff.getWorkStatus()).append("' ");
		}
		final String queryStr = queryCondton.toString();		
		
		Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
					return session.createQuery(queryStr).uniqueResult();
				}
			});
			return count.intValue();
	}
				
	@Override
	public StaffInfo getStaffInfoByUserId(final String userId) {
		StaffInfo si = (StaffInfo)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("from StaffInfo si where si.userId=? and si.status <>'D'");
				q.setString(0, userId);
				return q.list().size()>0?q.list().get(0):null;
			}
		});
		if(si!=null) {			
			log.debug(si.getName()+"的账户条数为:"+si.getAccounts().size());
			log.debug(si.getName()+"的合同条数为："+si.getContracts().size());
		}
		return si;
	}

	@Override
	public void updateAuditStatus(final String staffId) {
		StaffInfo si = (StaffInfo)this.getHibernateTemplate().get(StaffInfo.class, staffId);
		if(AuditStatus.AUDIT_PASSED.name().equals(si.getAuditStatus())) {
			si.setAuditStatus(AuditStatus.AUDIT_NOPASSED.name());
		} else {
			si.setAuditStatus(AuditStatus.AUDIT_PASSED.name());
		}
		final String auditStatus = si.getAuditStatus();
		
		getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("update StaffInfo si set si.auditStatus=? where si.staffId=?");
				q.setString(0, auditStatus);
				q.setString(1, staffId);
				return q.executeUpdate();
			}
		});
	}

	@Override
	public int updateDepartment(final String deptId) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q=session.createQuery("update StaffInfo si set si.department = null where si.department = ?");
				q.setString(0, deptId);
				return q.executeUpdate();
			}
		});
	}

	@Override
	public int updateServiceCustomer(final String scId) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q=session.createQuery("update StaffInfo si set si.serviceCustomer = null where si.serviceCustomer = ?");
				q.setString(0, scId);
				return q.executeUpdate();
			}
		});
	}

	@Override
	public void changeServiceCustomer(final String staffId, final ServiceCustomer sc) {
		getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("update StaffInfo si set si.serviceCustomer = ? where si.staffId = ?");
				q.setString(0, sc.getId());
				q.setString(1, staffId);
				return q.executeUpdate();
			}
		});
	}
}
